package main

import (
	"database/sql"
	"regexp"
	"strings"

	_ "github.com/go-sql-driver/mysql"
	Log "github.com/tsif/component/log"
)

// 表字段
type DBColumn struct {
	Name       string // 字段名
	Type       string // 字段类型
	Note       string // 字段注释
	DataType   string // 数据类型
	DataMaxLen uint64 // 数据大小
}

// 表结构
type DBTable struct {
	DBName    string     // DB名称
	TableName string     // 表名
	Columns   []DBColumn // 字段信息
	Note      string     // 注释
}

// 获取DB名称
func getDBName(db *sql.DB) (string, error) {
	dbname := ""
	err := db.QueryRow("select database();").Scan(&dbname)
	if err != nil {
		return "", err
	}
	return dbname, nil
}

// 获取表列表
func getTableNames(db *sql.DB, dbname string) (*[]string, error) {
	// 读取表列表
	rows, err_q := db.Query("select table_name from information_schema.`TABLES` where `TABLE_SCHEMA`=? ", dbname)
	if err_q != nil {
		return nil, err_q
	}
	defer rows.Close()

	// 读取表列表
	list := []string{}
	for rows.Next() {
		tablename := ""
		// 解析
		err_s := rows.Scan(&tablename)
		if err_s != nil {
			return nil, err_s
		}
		list = append(list, tablename)
	}
	return &list, nil
}

// 获取表基础数据
func getTableBaseInfo(db *sql.DB, table *DBTable) error {
	err_q := db.QueryRow("select `TABLE_COMMENT` from information_schema.`TABLES` where `TABLE_SCHEMA`=? and `TABLE_NAME`=?", table.DBName, table.TableName).Scan(&table.Note)
	return err_q
}

// 获取表字段信息
func getTableColumnInfo(db *sql.DB, table *DBTable) error {
	// 读取表信息
	rows, err_q := db.Query("select `COLUMN_NAME`,`COLUMN_COMMENT`,`COLUMN_TYPE`,`DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH` from information_schema.`COLUMNS` where `TABLE_SCHEMA`=? and `TABLE_NAME`=? ", table.DBName, table.TableName)
	if err_q != nil {
		return err_q
	}
	defer rows.Close()
	// 创建表字段
	column := DBColumn{}
	var dataLen sql.NullInt64

	// 提取信息
	for rows.Next() {
		// 解析
		err_s := rows.Scan(&column.Name, &column.Note, &column.Type, &column.DataType, &dataLen)
		if err_s != nil {
			return err_s
		}
		column.DataMaxLen = uint64(dataLen.Int64)
		table.Columns = append(table.Columns, column)

		// Log.Info("table: ", tablename, " columu:", column.Name, column.Type,
		// 	column.DataType, column.DataMaxLen)
	}
	return nil
}

// 获取表信息
func getTableInfo(db *sql.DB, dbname string, tablename string) (*DBTable, error) {
	// 创建表字段
	table := DBTable{DBName: dbname, TableName: tablename}
	// 提取表基础信息
	err_b := getTableBaseInfo(db, &table)
	if err_b != nil {
		return nil, err_b
	}
	// 提取字段信息
	err_c := getTableColumnInfo(db, &table)
	if err_c != nil {
		return nil, err_c
	}
	return &table, nil
}

// 解析DB
func DBParse(url string, tableRegx string) (*[]DBTable, error) {
	// url = "root:admin@tcp(localhost:3306)"
	// connect
	db, err_op := sql.Open("mysql", url)
	if err_op != nil {
		return nil, err_op
	}
	defer db.Close()

	// 获取DB
	dbname, err_n := getDBName(db)
	if err_n != nil {
		return nil, err_n
	}

	// 读取表
	tablenames, err_t := getTableNames(db, dbname)
	if err_t != nil {
		return nil, err_t
	}

	// 正则筛选对象
	regx := regexp.MustCompile(tableRegx)

	// 遍历读取表结构
	list := []DBTable{}
	for _, tablename := range *tablenames {
		// 表名正则筛选
		c := regx.FindString(tablename)
		if c == "" {
			continue
		}

		// 读取表结果
		dbinfo, err_t := getTableInfo(db, dbname, tablename)
		if err_t != nil {
			Log.Error("get table info fail! ", err_t.Error())
		}
		list = append(list, *dbinfo)
	}

	return &list, nil
}

// 识别为通用类型
func getType(column *DBColumn) (dtype int, isarray bool) {
	// Log.Info("columu:", column.Name, column.Type, column.DataType, column.DataMaxLen)
	columnType := strings.ToUpper(column.Type)   // 类型大写
	dataType := strings.ToUpper(column.DataType) // 类型大写

	// 是否无符号
	isUnsigned := strings.Index(columnType, "UNSIGNED") >= 0
	// Log.Info(columnType, isUnsigned, dataType)

	// 识别类型
	if "BIT" == dataType {
		// 1个byte认为是布尔
		if column.DataMaxLen > 1 {
			return FIELD_TYPE_BYTE, true
		}
		return FIELD_TYPE_BOOL, false
	} else if "TINYINT" == dataType {
		if column.DataMaxLen == 1 && "tinyint(1)" == columnType {
			return FIELD_TYPE_BOOL, false
		}
		if isUnsigned {
			return FIELD_TYPE_USHORT, false
		}
		return FIELD_TYPE_SHORT, false
	} else if "BOOL" == dataType ||
		"BOOLEAN" == dataType {
		return FIELD_TYPE_BOOL, false
	} else if "SMALLINT" == dataType {
		if isUnsigned {
			return FIELD_TYPE_USHORT, false
		}
		return FIELD_TYPE_SHORT, false
	} else if "MEDIUMINT" == dataType {
		if isUnsigned {
			return FIELD_TYPE_UINT, false
		}
		return FIELD_TYPE_INT, false
	} else if "INTEGER" == dataType ||
		"INT" == dataType {
		if isUnsigned {
			return FIELD_TYPE_UINT, false
		}
		return FIELD_TYPE_INT, false
	} else if "BIGINT" == dataType {
		if isUnsigned {
			return FIELD_TYPE_ULONG, false
		}
		return FIELD_TYPE_LONG, false
	} else if "FLOAT" == dataType {
		return FIELD_TYPE_FLOAT, false
	} else if "DOUBLE" == dataType {
		return FIELD_TYPE_DOUBLE, false
	} else if "DECIMAL" == dataType {
		return FIELD_TYPE_DOUBLE, false
	} else if "DATE" == dataType ||
		"DATETIME" == dataType ||
		"TIMESTAMP" == dataType ||
		"TIME" == dataType ||
		"YEAR" == dataType {
		return FIELD_TYPE_DATE, false
	} else if "CHAR" == dataType ||
		"TINYTEXT" == dataType ||
		"TEXT" == dataType ||
		"MEDIUMTEXT" == dataType ||
		"LONGTEXT" == dataType ||
		"VARCHAR" == dataType {
		return FIELD_TYPE_STRING, false
	} else if "BINARY" == dataType ||
		"VARBINARY" == dataType ||
		"TINYBLOB" == dataType ||
		"BLOB" == dataType ||
		"MEDIUMBLOB" == dataType ||
		"LONGBLOB" == dataType {
		return FIELD_TYPE_BYTE, true
	}
	return FIELD_TYPE_UNKOWN, false
}

// 转化成订单
func makdeOrder(table *DBTable) (*MakerOrder, error) {

	order := MakerOrder{Name: table.TableName, Note: table.Note}

	// 处理属性
	for _, column := range table.Columns {
		field := MakerField{Name: column.Name, Note: column.Note}
		field.Type, field.IsArray = getType(&column)
		// Log.Info("order: ", order.Name, " fied:", field.Name, field.Type, field.IsArray)
		// 加入列表
		order.Fields = append(order.Fields, field)
	}

	return &order, nil
}

// 转化成订单
func MakeOrder(tables *[]DBTable, match string) (*[]MakerOrder, error) {
	// 遍历table
	list := []MakerOrder{}
	for _, table := range *tables {
		// 检测过滤

		// 转化
		order, err_o := makdeOrder(&table)
		if err_o != nil {
			return nil, err_o
		}
		// 空跳过
		if order == nil {
			continue
		}
		// 加入列表
		list = append(list, *order)
	}

	return &list, nil
}
